import os
import pandas as pd
from DrissionPage import ChromiumPage, ChromiumOptions
from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String, TIMESTAMP, Date, text
from sqlalchemy.dialects.mysql import DOUBLE
from datetime import datetime
import pyautogui

current_datetime = datetime.now()

# 格式化为 MySQL 日期时间格式（YYYY-MM-DD HH:MM:SS）
mysql_datetime_format = current_datetime.strftime('%Y-%m-%d %H:%M:%S')
file_path = r'E:\google_download'

host = 'rm-2zea30h4sh8g15zd1ho.mysql.rds.aliyuncs.com'
user = 'ai'
password = 'Cpt123456@'
database = 'doudianhoutai'
# 构建连接字符串
connection_string = f'mysql+pymysql://{user}:{password.replace("@", "%40")}@{host}/{database}'
# 创建数据库引擎
engine = create_engine(connection_string)


def convert_excel_to_mysqltable(data, table_name, engine=engine):
    # 定义表结构
    meta = MetaData()
    table_name = table_name
    try:
        # 尝试反射表
        inspector = MetaData()
        inspector.reflect(bind=engine, only=[table_name])
        print(f"表 {table_name} 已存在")
    except Exception as e:
        # 如果表不存在，创建表
        print(f"表 {table_name} 不存在，将创建它: {e}")
        # 定义列及其数据类型
        columns = []
        for column_name, dtype in data.dtypes.items():
            columns.append(Column(column_name, String(length=100)))  # 默认使用字符串类型
        Table(
            table_name, meta,
            Column('id', Integer, primary_key=True, autoincrement=True),
            *columns,
            Column('创建时间', TIMESTAMP, server_default=text('CURRENT_TIMESTAMP')),
        )
        meta.create_all(engine)
        print(f"表 {table_name} 创建成功")
    # 将 DataFrame 追加到 MySQL 数据库中的表 '全店成交分析_成交概览'
    data.to_sql(name=table_name, con=engine, if_exists='append', index=False)
    print("数据已成功追加到 MySQL 数据库")


driver = ChromiumPage()
driver.set.window.max()
driver.wait(1)
driver.get(
    r'https://yuntu.oceanengine.com/yuntu_brand/ecom/strategy/medium/talent_markting/hotcontent?aadvid=1791680489036811')
driver.wait(3)
driver.eles(
    '.strategy-ecom-input-inner__wrapper strategy-ecom-input-inner__wrapper-border strategy-ecom-input-inner__wrapper-size-md strategy-ecom-input-inner__wrapper-add-suffix strategy-ecom-input-inner__wrapper-filled')[
    -2].click()
driver.wait(5)
driver.eles('.strategy-ecom-list-item-inner-wrapper strategy-ecom-select-option-inner-wrapper')[-1].click()
driver.wait(0.5)
pyautogui.moveTo(1000, 500)
driver.wait(0.5)
pyautogui.scroll(-1000)
driver.wait(2)
# 下载文件
driver.ele('xpath://*[@id="talent_rank"]/div[5]/div[2]/div[2]/div/div/div[2]/div[2]/span[2]/div').click()
driver.wait(2)
driver.ele('xpath://*[@id="quality"]/div').click()
driver.wait(10)
# 数据下载完毕,写入数据库
files = os.listdir(file_path)
csv_file = files[0]

# 构建完整的 CSV 文件路径
full_csv_path = os.path.join(file_path, csv_file)

# 读取 CSV 文件
data = pd.read_csv(full_csv_path)

# 写入数据库
table_name = '云图_爆文加热_月抓'  # 使用文件名作为表名
convert_excel_to_mysqltable(data=data, table_name=table_name)

# 删除文件
os.remove(full_csv_path)
print('删除文件成功')

# 关闭浏览器驱动
driver.quit()